﻿USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for Phase table...'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectPhaseById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectPhaseById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectPhaseById]
    @Phase_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Phase] WHERE Phase_Id = @Phase_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertPhase]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertPhase]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertPhase]
    @Project_Id int,
    @Title nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Phase] 
	(
	Project_Id, 
	Title
	)
VALUES 
	(
	@Project_Id, 
	@Title
	)

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdatePhase]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdatePhase]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdatePhase]
    @Phase_Id int,
    @Project_Id int,
    @Title nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Phase] 
SET 
	Project_Id = @Project_Id, 
	Title = @Title
WHERE Phase_Id = @Phase_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeletePhase]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeletePhase]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeletePhase]
    @Phase_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [Phase] 
WHERE Phase_Id = @Phase_Id

END
'
GO 